package com.power.sifa.supervise.dao.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.plf.core.common.dao.OffersetTransformers;
import com.power.sifa.approve.model.Apl_arrive_main;
import com.power.sifa.supervise.dao.Apl_superviseDaoCustom;
import com.power.sifa.supervise.model.Apl_supervise;
import com.power.sifa.supervise.model.pageInfo.Apl_supervisePageInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.query.internal.NativeQueryImpl;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
* 复议监督主表 持久层实现(hibernate)
*/

@Repository
@Slf4j
public class Apl_superviseDaoImpl implements Apl_superviseDaoCustom {

    @Resource
    private EntityManager entityManager;


    @Override
    public List findList(Apl_supervisePageInfo pageInfo) {
        List<Apl_supervise> result = new ArrayList<>();

        StringBuffer sql = new StringBuffer("select A.*, B.applycode,B.regusername,B.createtime, B.id as applyid, B" +
                ".acceptcode, B.source as source, B.status  from  Apl_supervise A " +
                " inner join apl_main B ON A.id = B.id where 1 = 1 ");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;
        // 由申请流水号调整为案号查询
        if (StringUtils.isNotEmpty(pageInfo.getF_acceptcode())) {
            sql.append(" and B.acceptcode like ?" + index++);
            paramsList.add("%" + pageInfo.getF_acceptcode() + "%");
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_applyname())) {
            sql.append(" and A.applyname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_applyname() + "%");
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_targetname())) {
            sql.append(" and A.targetname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_targetname() + "%");
        }
        if (ObjectUtil.isNotNull(pageInfo.getF_source())) {
            sql.append(" and B.source = ?" + index++);
            paramsList.add(pageInfo.getF_source());
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getF_applystarttime())) {
            sql.append(" and B.createtime >= ?" + index++);
            paramsList.add(pageInfo.getF_applystarttime());
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getF_applyendtime())) {
            sql.append(" and B.createtime <= ?" + index++);
            paramsList.add(pageInfo.getF_applyendtime());
        }
        // 状态
        if (!CollectionUtil.isEmpty(pageInfo.getF_status())) {
            sql.append(" and B.status in ?" + index++);
            paramsList.add(pageInfo.getF_status());
        }

        // 排序
        sql.append(" order by B." + pageInfo.getSort() + " " + pageInfo.getOrder());
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        log.info(sql.toString());

        // 添加条件
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        // 查询总数
        pageInfo.setAllcount(query.getResultList().size());
        // 分页
        if (pageInfo.getAllcount() > 0) {
            int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
            List<Map> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
            if (CollectionUtil.isNotEmpty(list)) {
                result = BeanUtil.copyToList(list, Apl_supervise.class);
//                result = JSON.parseArray(JSON.toJSONString(list), Apl_supervise.class);
            }
        }
        return result;
    }

    @Override
    public List<Apl_arrive_main> querySuperviseArriveData(Apl_supervisePageInfo pageInfo) {
        List<Apl_arrive_main> result = new ArrayList<>();

        //  排除未提交的状态
        StringBuffer sql = new StringBuffer("select A.*, B.acceptcode,B.status, B.source, C.tosendnum,C.warntime,D.sendednum from apl_supervise A left join apl_main B ON A.id = B.id \n" +
                "left join (select count(id) as toSendNum,applyid,min(warntime) as warntime, sendstatus from Apl_arrive_detail GROUP BY applyid,sendstatus HAVING sendstatus = 3201) C ON A.id = C.applyid\n" +
                "left join (select count(id) as sendedNum,applyid,min(warntime) as warntime, sendstatus from Apl_arrive_detail GROUP BY applyid,sendstatus HAVING sendstatus = 3200) D ON A.id = D.applyid\n" +
                "where 1=1 AND B.status != 6000600 ");

        List<Object> paramsList = new ArrayList<>();
        int index = 1;
        // 案号查询
        if (StringUtils.isNotEmpty(pageInfo.getF_acceptcode())) {
            sql.append(" and B.acceptcode like ?" + index++);
            paramsList.add("%" + pageInfo.getF_acceptcode() + "%");
        }
        // 流程matid查询
        if (StringUtils.isNotEmpty(pageInfo.getF_matid())) {
            sql.append(" and B.matid = ?" + index++);
            paramsList.add(pageInfo.getF_matid() );
        }
        // 申请人查询
        if (StringUtils.isNotEmpty(pageInfo.getF_applyname())) {
            sql.append(" and A.applyname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_applyname() + "%");
        }
        // 被申请人查询
        if (StringUtils.isNotEmpty(pageInfo.getF_targetname())) {
            sql.append(" and A.targetname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_targetname() + "%");
        }
        // 申请来源
        if (ObjectUtil.isNotEmpty(pageInfo.getF_source())) {
            sql.append(" and B.source = ?" + index++);
            paramsList.add(pageInfo.getF_source());
        }

        if (StringUtils.isNotEmpty(pageInfo.getF_applystarttime())) {
            sql.append(" and C.warntime >= ?" + index++);
            paramsList.add(pageInfo.getF_applystarttime());
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_applyendtime())) {
            sql.append(" and C.warntime <= ?" + index++);
            paramsList.add(pageInfo.getF_applyendtime());
        }

        // 行政复议状态 f_status
        if (!CollectionUtil.isEmpty(pageInfo.getF_status())) {
            sql.append(" and B.status in ?" + index++);
            paramsList.add(pageInfo.getF_status());
        }

        // 排序
        sql.append(" order by warntime asc, B.createtime desc ");
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        log.info(sql.toString());

        // 添加条件
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        // 查询总数
        pageInfo.setAllcount(query.getResultList().size());
        // 分页
        if (pageInfo.getAllcount() > 0) {
            int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
            List<Map> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
            if (CollectionUtil.isNotEmpty(list)) {
                result = JSON.parseArray(JSON.toJSONString(list), Apl_arrive_main.class);
            }
        }
        return result;
    }

    @Override
    public List queryArchiveData(Apl_supervisePageInfo pageInfo) {
        List<Apl_supervise> result = new ArrayList<>();
        // 按照对应状态进行区分，已完结状态优先级排序在前
        StringBuffer sql = new StringBuffer(" SELECT A .ID AS applyid, A.applycode AS applycode, A.acceptcode AS acceptcode, A.instid, A.SOURCE, A.status, A.finishtime AS decisiontime, A.archivetime AS archivetime, A.subtime AS createtime, B.* FROM Apl_main A INNER JOIN apl_supervise B ON A.ID = B.ID WHERE 1 = 1 ");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;
        // 案号查询
        if (StringUtils.isNotEmpty(pageInfo.getF_acceptcode())) {
            sql.append(" and A.acceptcode like ?" + index++);
            paramsList.add("%" + pageInfo.getF_acceptcode() + "%");
        }
        // 申请人查询
        if (StringUtils.isNotEmpty(pageInfo.getF_applyname())) {
            sql.append(" and B.applyname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_applyname() + "%");
        }
        // 被申请人查询
        if (StringUtils.isNotEmpty(pageInfo.getF_targetname())) {
            sql.append(" and B.targetname like ?" + index++);
            paramsList.add("%" + pageInfo.getF_targetname() + "%");
        }
        // 申请来源
        if (ObjectUtil.isNotEmpty(pageInfo.getF_source())) {
            sql.append(" and A.source = ?" + index++);
            paramsList.add(pageInfo.getF_source());
        }

        // 申请时间
        if (ObjectUtil.isNotEmpty(pageInfo.getF_applystarttime())) {
            sql.append(" and A.subtime >= ?" + index++);
            paramsList.add(pageInfo.getF_applystarttime());
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getF_applyendtime())) {
            sql.append(" and A.subtime <= ?" + index++);
            paramsList.add(pageInfo.getF_applyendtime());
        }

        // 行政复议状态 f_status
        if (!CollectionUtil.isEmpty(pageInfo.getF_status())) {
            sql.append(" and A.status in ?" + index++);
            paramsList.add(pageInfo.getF_status());
        }

        // 排序
        sql.append(" order by subtime desc, " + pageInfo.getSort() + " " + pageInfo.getOrder());
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        log.info(sql.toString());

        // 添加条件
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        // 查询总数
        pageInfo.setAllcount(query.getResultList().size());
        // 分页
        if (pageInfo.getAllcount() > 0) {
            int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
            List<Map> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
            if (CollectionUtil.isNotEmpty(list)) {
                result = JSON.parseArray(JSON.toJSONString(list), Apl_supervise.class);
            }
        }
        return result;
    }
}